package reservation;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class QueryRunner 
{
	private String queryResult;
	
	public QueryRunner()
	{
		
	}
	public String Execute(String reservationID)
	{
		if(reservationID == null || reservationID.equals(""))
		{
			System.err.println("No value passed to the cancel method");
		}
		try
		{
			this.openConnection("UPDATE TABLE RESERVATION SET ReservationStatus = 'C' WHERE ReservationID = " + 
					reservationID + ";");
			return "Success";
		}
		catch(SQLException se)
		{
			return "Fail";
		}
	}
	public String[][] getAllReservations()
	{
		try
		{
			return openConnection("SELECT *FROM ROOM ro, RESERVATION re WHERE ro.RoomID = re.RoomID AND NOW() < re.ReservationStart;\n");
		}
		catch(SQLException se)
		{
			return se.getMessage();
		}
	}
	
	public String[][] getReservesWithin(String date1, String date2)
	{
		String query = "";
		if(date1 != null && !date1.equals(""))
		{
			query += "SET @date1 = '"+ date1 + "'\n";

		}
		else
		{
			query += "SET @date1 = NOW()\n";
		}
		if(date2 != null)
		{
			query += "SET @date2 = '"+date2+"'\n";
		}
		else
		{
			return null;
		}
		query += "SELECT * FROM ROOM ro, RESERVATION re WHERE ro.RoomID = re.RoomID AND @date1 < re.ReservationStart AND re.ReservationStart < @date2;\n";
		try
		{
			return openConnection(query);
		}
		catch(SQLException se)
		{
			return se.getMessage();
		}
	}
	public String[][] getUserReservesWithin(String name, String date1, String date2)
	{
		String query = "";
		if(name == null || name.equals(""))
		{
			return this.getReservesWithin(date1, date2);
		}
		else
		{
			query += "SET @name = '"+ name + "';\n";			
		}
		if(date1 != null && !date1.equals(""))
		{
			query += "SET @date1 = '"+ date1 + "';\n";

		}
		else
		{
			query += "SET @date1 = NOW();\n";
		}
		if(date2 != null)
		{
			query += "SET @date2 = '"+date2+"';\n";
		}
		else
		{
			return null;
		}
		query += "SELECT * FROM ROOM ro, RESERVATION re WHERE ro.RoomID = re.RoomID AND @date1 < re.ReservationStart AND re.ReservationStart < @date2 AND re.UserID = @name AND re.ReservationStatus != 'O' AND re.ReservationStatus != 'C';\n";
		try
		{
			return openConnection(query);
		}
		catch(SQLException se)
		{
			return se.getMessage();
		}
	}
	public String[][] getAvailableWithin(String date1, String date2)
	{
		String query = "";
		if(date1 != null && !date1.equals(""))
		{
			query += "SET @date1 = '"+ date1 + "'\n";

		}
		else
		{
			query += "SET @date1 = NOW();\n";
		}
		if(date2 != null)
		{
			query += "SET @date2 = '"+date2+"';\n";
		}
		else
		{
			return null;
		}
		query += "SELECT * FROM ROOM re1 WHERE re1.RoomID NOT IN (SELECT re.RoomID FROM ROOM ro, RESERVATION re WHERE ro.RoomID = re.RoomID AND @date1 < re.ReservationStart AND re.ReservationStart < @date2;\n";
		try
		{
			return openConnection(query);
		}
		catch(SQLException se)
		{
			return se.getMessage();
		}
	}

	public String[][] getAvailableFor(String date1, String date2, String roomid)
	{
		String query = "";
		if(date1 != null && !date1.equals(""))
		{
			query += "SET @date1 = '"+ date1 + "';\n";

		}
		else
		{
			query += "SET @date1 = NOW();\n";
		}
		if(date2 != null)
		{
			query += "SET @date2 = '"+date2+"';\n";
		}
		else
		{
			return null;
		}
		if(roomid != null)
		{
			query += "SET @roomID = '"+roomid+"';\n";
		}
		else
		{
			return null;
		}

		query += "SELECT * FROM RESERVATION re WHERE re.RoomID = @roomID AND @date1 < re.ReservationStart AND re.ReservationStart < @date2;";
		try
		{
			return openConnection(query);
		}
		catch(SQLException se)
		{
			return se.getMessage();
		}
	}
	public String[][] getAvailableFor(String date1, String date2, int roomno)
	{
		String query = "";
		if(date1 != null && !date1.equals(""))
		{
			query += "SET @date1 = '"+ date1 + "'";

		}
		else
		{
			query += "SET @date1 = NOW();\n";
		}
		if(date2 != null)
		{
			query += "SET @date2 = '"+date2+"'";
		}
		else
		{
			return null;
		}
		query += "SET @roomID = '"+roomno+"';\n";

		query += "SELECT * FROM RESERVATION re WHERE re.RoomID IN (SELECT re.RoomID FROM ROOM ro, RESERVATION re WHERE re.RoomID = ro.RoomID AND ro.RoomNumber = @roomNumber AND @date1 < re.ReservationStart AND re.ReservationStart < @date2;";
		try
		{
			return openConnection(query);
		}
		catch(SQLException se)
		{
			throw se;
		}
	}

	
	
	public String[][] openConnection(String statementSet) throws SQLException
	{
		Connection connection = null;
		String[][] queryResult;
		try{
			DriverManager.registerDriver(new com.mysql.jdbc.Driver());
			connection = DriverManager.getConnection("jdbc:mysql://137.190.19.20:3306/reservations","reservations","Hello912");
			
			Statement sqlStatement = connection.createStatement();
			ResultSet sqlResult = sqlStatement.executeQuery(statementSet);
			sqlResult.last();
			queryResult = new String[sqlResult.getRow()][sqlResult.getMetaData().getColumnCount()];
			sqlResult.first();
			int i = 0;
			while(sqlResult.next())
			{
				for (int j = 0; j < sqlResult.getMetaData().getColumnCount(); j++)
				queryResult[i][j] sqlResult.toString(j);
				i++;
			}
			sqlResult.close();
			sqlStatement.close();
						
		}catch(Exception e){
			System.out.println("Exception : " + e);
			
		}finally{
			connection.close();
		}
		return queryResult;
	}
}
